package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

var db *sql.DB

func main() {

	dsn := "root:123456@tcp(127.0.0.1:3306)/bbs"
	var err error
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		panic(err)
	}
	err = db.Ping()
	if err != nil {
		panic(err)
	}

	// 读未提交
	// 脏读
	//DirtyRead("READ-UNCOMMITTED")
	// 不可重复度
	//NonRepeatableRead("READ-UNCOMMITTED")
	// 幻读
	//PhantomRead("READ-UNCOMMITTED")

	// 读已提交
	// 脏读
	//DirtyRead("READ-COMMITTED")
	// 不可重复度
	// NonRepeatableRead("READ-COMMITTED")
	// 幻读
	//PhantomRead("READ-COMMITTED")

	// 可重复度-幻读
	// 脏读
	//DirtyRead("REPEATABLE-READ")
	// 不可重复度
	//NonRepeatableRead("REPEATABLE-READ")
	// 幻读
	// PhantomRead("REPEATABLE-READ")

	// 为什么更新后才出现幻读？
	// 未更新时：conn1的查询基于事务启动时的一致性视图，conn2新增的记录不在视图中，因此第一次查询看不到，符合 “可重复读” 的隔离承诺。 快照读
	// 更新之后：更新操作强制读取最新数据并修改了新记录，导致conn1的视图被 “打破”，后续查询会包含这条已被修改的新记录，表现为 “幻读”。 当前读

}

func DirtyRead(isolation string) {
	db.Exec(fmt.Sprintf("SET session transaction_isolation = '%s'", isolation))
	var isolationLevel string
	var err error
	err = db.QueryRow("SELECT @@transaction_isolation;").Scan(&isolationLevel)
	if err != nil {
		panic(err)
	}
	fmt.Printf("当前事务隔离级别: %s\n", isolationLevel)

	conn1, err := db.Begin()
	if err != nil {
		fmt.Println("begin failed :", err)
		return
	}

	conn2, err := db.Begin()
	if err != nil {
		fmt.Println("begin failed :", err)
		return
	}

	fmt.Println("conn2插入一条数据")
	conn2.Exec("insert into city(name) values('上海')")

	fmt.Println("conn1读取数据")
	cityRows(conn1, "select id,name from city")

	fmt.Println("conn2回滚")
	conn2.Rollback()

	fmt.Println("conn1再读取数据")
	cityRows(conn1, "select id,name from city")

	conn1.Commit()
}

func NonRepeatableRead(isolation string) {
	db.Exec(fmt.Sprintf("SET session transaction_isolation = '%s'", isolation))
	var isolationLevel string
	var err error
	err = db.QueryRow("SELECT @@transaction_isolation;").Scan(&isolationLevel)
	if err != nil {
		panic(err)
	}
	fmt.Printf("当前事务隔离级别: %s\n", isolationLevel)

	conn1, err := db.Begin()
	if err != nil {
		fmt.Println("begin failed :", err)
		return
	}

	conn2, err := db.Begin()
	if err != nil {
		fmt.Println("begin failed :", err)
		return
	}

	fmt.Println("conn1读取数据")
	cityRows(conn1, "select id,name from city")

	fmt.Println("conn2更新一条数据")
	conn2.Exec("update city set name='上海123' where id=1")

	conn2.Commit()
	fmt.Println("conn2提交")

	fmt.Println("conn1再读取数据")
	cityRows(conn1, "select id,name from city")

	conn1.Commit()
}

func PhantomRead(isolation string) {
	db.Exec(fmt.Sprintf("SET session transaction_isolation = '%s'", isolation))
	var isolationLevel string
	var err error
	err = db.QueryRow("SELECT @@transaction_isolation;").Scan(&isolationLevel)
	if err != nil {
		panic(err)
	}
	fmt.Printf("当前事务隔离级别: %s\n", isolationLevel)

	conn1, err := db.Begin()
	if err != nil {
		fmt.Println("begin failed :", err)
		return
	}

	conn2, err := db.Begin()
	if err != nil {
		fmt.Println("begin failed :", err)
		return
	}

	fmt.Println("conn1读取数据")
	cityRows(conn1, "select id,name from city")

	fmt.Println("conn2插入一条数据")
	conn2.Exec("insert into city(id,name) values(10, '北京')")

	conn2.Commit()
	fmt.Println("conn2提交")

	conn1.Exec("update city set name='上海' where id=10")

	fmt.Println("conn1再读取数据")
	cityRows(conn1, "select id,name from city")

	conn1.Commit()
}

type City struct {
	id   int
	name string
}

func cityRows(db *sql.Tx, sql string) {
	var err error
	rows, err := db.Query(sql)
	if err != nil {
		panic(err)
	}
	var cities []*City
	for rows.Next() {
		var city City
		rows.Scan(&city.id, &city.name)
		cities = append(cities, &city)
	}

	for _, city := range cities {
		fmt.Printf("city.id=%d name=%s\n", city.id, city.name)
	}
}
